package com.bdqn.repository;

import com.bdqn.pojo.UserVo;
import com.bdqn.pojo.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * UserRepository
 *
 * @author LILIBO
 * @since 2022-01-16
 */
public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {

    /**
     * 通过UsrName查找用户
     *
     * @param usrName
     * @return
     */
    List<User> findByUsrNameLike(String usrName);

    /**
     * 通过UsrName属性和UsrPassword属性查询
     * <p>
     * select * from sys_user where usr_name=? and usr_password=?
     *
     * @param usrName
     * @param usrPassword
     * @return
     */
    List<User> findByUsrNameAndUsrPassword(String usrName, String usrPassword);

    /**
     * 使用自定义QL语句完成复杂查询
     *
     * @param roleId
     * @return
     */
    // @Query("select u from User u where u.usrRoleId=?1") // 与SQL语句的区别：将表名变为实体名称，将字段名变为属性名称
    // @Query(value = "select * from sys_user where usr_role_id=?1", nativeQuery = true) // 想用原生SQL语句：nativeQuery = true
    @Query(value = "select * from sys_user where usr_role_id=:roleId", nativeQuery = true)
    List<User> findByRoleId(@Param("roleId") Long roleId);

    /**
     * 对事务的支持
     *
     * @param usrName
     * @param usrId
     * @return
     */
    @Transactional(timeout = 10)
    @Modifying
    @Query("update User u set u.usrName=?1 where u.usrId=?2")
    int modifyNameById(String usrName, Long usrId);

    /**
     * 通过将方法名与定义的命名QL一致
     * @param usrName
     * @return
     */
    List<User> findUsersByName(String usrName);

    /**
     * 分页查询
     *
     * @param roleId 自己的参数写在前面
     * @param pageable 内置的分页对象
     * @return Page（Page查询分页对象以及数据；Slice表示只查询一页数据）
     */
    @Query("select u from User u where u.usrRoleId=?1") // 多对一关联后已经没有usrRoleId了
    Page<User> findPageByUsrRoleId(Long roleId, Pageable pageable);

    /**
     * 根据UserID查询用户信息，包含用户角色名称（关联查询）
     *
     * @param usrId
     * @return
     */
    @Query("select u.usrId as usrId, u.usrName as usrName, u.usrPassword as usrPassword, u.usrRoleId as usrRoleId, u.usrFlag as usrFlag, r.roleName as roleName from User u, Role r where u.usrRoleId=r.roleId and u.usrId=?1")
    UserVo getUserVo(Long usrId);
}
